﻿
CREATE TABLE PESSOA(
  idPessoa serial PRIMARY KEY,
  anotacao varchar(800),
  url varchar(800)
);

CREATE TABLE NOME(
  idPessoa serial PRIMARY KEY,
  nome varchar(100)
);
ALTER TABLE NOME ADD CONSTRAINT FK_PESSOA_NOME FOREIGN KEY(idPessoa) REFERENCES PESSOA(idPEssoa) ;

CREATE TABLE RELACIONAMENTO(
  idPessoa1 integer,
  idPessoa2 integer,
  idTipoRel integer,
  idDocumento integer
);


CREATE TABLE DOCUMENTO(
  idDocumento serial PRIMARY KEY,
  ee varchar(800),
  paginas varchar(50),
  url varchar(800),
  titulo varchar(800),
  ano integer,
  mes integer,
  anotacao varchar(800),
  publisher varchar(800),
  id integer  
);

CREATE TABLE TIPORELACIONAMENTO(
  idTipoRel serial PRIMARY KEY,
  peso integer,
  status VARCHAR(1),
  tipo varchar(20)
);
ALTER TABLE RELACIONAMENTO ADD CONSTRAINT PK_RELACIONAMENTO PRIMARY KEY(idPessoa1,idPEssoa2,idTipoRel,idDocumento);
ALTER TABLE RELACIONAMENTO ADD CONSTRAINT FK_REL_PES1 FOREIGN KEY(idPEssoa1) REFERENCES PESSOA(idPessoa);
ALTER TABLE RELACIONAMENTO ADD CONSTRAINT FK_REL_PES2 FOREIGN KEY(idPEssoa2) REFERENCES PESSOA(idPessoa);
ALTER TABLE RELACIONAMENTO ADD CONSTRAINT FK_REL_TIPOREL FOREIGN KEY(idTipoRel) REFERENCES TIPORELACIONAMENTO(idTipoRel);
ALTER TABLE RELACIONAMENTO ADD CONSTRAINT FK_REL_DOCUMENTO FOREIGN KEY(idDocumento) REFERENCES DOCUMENTO(idDocumento);


CREATE TABLE MESTRADO(
  id serial PRIMARY KEY,
  idDocumento integer
);
ALTER TABLE MESTRADO ADD CONSTRAINT FK_DOC_MESTRADO FOREIGN KEY(idDocumento) REFERENCES DOCUMENTO(idDocumento);


CREATE TABLE TESE(
  idTese serial PRIMARY KEY,	
  isbn varchar(200),
  serie integer,
  numero integer,
  idDocumento integer
);
ALTER TABLE TESE ADD CONSTRAINT FK_DOC_TESE FOREIGN KEY(idDocumento) REFERENCES DOCUMENTO(idDocumento);

CREATE TABLE MEIOPUBLICACAO(
  idMeioP serial PRIMARY KEY,
  idLocal integer,
  ano integer,
  mes integer,
  nome varchar(1000),
  volume varchar(100)	
);

CREATE TABLE LOCAL(
  idLocal serial PRIMARY KEY,
  cidade varchar(40),
  pais varchar(20)
);

ALTER TABLE MEIOPUBLICACAO ADD CONSTRAINT FK_MEIO_LOCAL FOREIGN KEY(idLocal) REFERENCES LOCAL(idLocal);

CREATE TABLE ARTIGO(
  idArtigo serial PRIMARY KEY,
  cdRom varchar(200),
  idDocumento integer,
  idMeioP integer,
  tituloLivro varchar(800)
);
ALTER TABLE ARTIGO ADD CONSTRAINT FK_DOC_ARTIGO FOREIGN KEY(idDocumento) REFERENCES DOCUMENTO(idDocumento);
ALTER TABLE ARTIGO ADD CONSTRAINT FK_ARTIGO_MEIO FOREIGN KEY(idMeioP) REFERENCES MEIOPUBLICACAO(idMeioP);

CREATE TABLE LIVRO(
  idLivro serial PRIMARY KEY,
  serie integer,
  cdRom varchar(200),
  idDocumento integer
);
ALTER TABLE LIVRO ADD CONSTRAINT FK_DOC_LIVRO FOREIGN KEY(idDocumento) REFERENCES DOCUMENTO(idDocumento);

CREATE TABLE TCC(
  idTcc serial PRIMARY KEY,
  idDocumento integer
);
ALTER TABLE TCC ADD CONSTRAINT FK_DOC_TCC FOREIGN KEY(idDocumento) REFERENCES DOCUMENTO(idDocumento);

CREATE TABLE FUNCAO(
  idFuncao serial PRIMARY KEY,
  nome varchar(40)
);

CREATE TABLE INSTITUICAO(
  idInstituicao serial PRIMARY KEY,
  idLocal integer,
  nome varchar(40)
);
ALTER TABLE INSTITUICAO ADD CONSTRAINT FK_INST_LOCAL FOREIGN KEY(idLocal) REFERENCES LOCAL(idLocal);

CREATE TABLE ALOCACAO(
  idAloca serial PRIMARY KEY,
  idFuncao integer,
  dataAloc date,
  idInst integer
);
ALTER TABLE ALOCACAO ADD CONSTRAINT FK_ALOCA_FUNCAO FOREIGN KEY(idFuncao) REFERENCES FUNCAO(idFuncao);
ALTER TABLE ALOCACAO ADD CONSTRAINT FK_ALOCA_INST FOREIGN KEY(idInst) REFERENCES INSTITUICAO(idInstituicao);

CREATE TABLE JOURNAL(
  idJournal serial PRIMARY KEY,
  numero varchar(10),
  idMeioP integer
);
ALTER TABLE JOURNAL ADD CONSTRAINT FK_JORN_MEIO FOREIGN KEY(idMeioP) REFERENCES MEIOPUBLICACAO(idMeioP);

CREATE TABLE CONGRESSO(
  idCongresso serial PRIMARY KEY,
  idMeioP integer,
  url varchar(800),
  serie integer,
  isbn varchar(800),
  ee varchar(800),
  publisher varchar(400),
  crossref varchar(40) unique,
  tituloLivro varchar(400),
  series varchar(200),
  idJournal integer
);
ALTER TABLE CONGRESSO ADD CONSTRAINT FK_CONG_MEIO FOREIGN KEY(idMeioP) REFERENCES MEIOPUBLICACAO(idMeioP);
ALTER TABLE CONGRESSO ADD CONSTRAINT FK_JORNAL FOREIGN KEY(idJournal) REFERENCES JOURNAL(idJournal);


CREATE TABLE PARAMETROS(
  dtUltCarga date,
  tempoGasto integer,
  numPessoas integer,
  numDocumentos integer,
  numEventos integer
);

CREATE TABLE EDITORACAO(
  IDCONGRESSO INTEGER,	
  IDPESSOA INTEGER
);
ALTER TABLE EDITORACAO ADD CONSTRAINT PK_EDITORCAO PRIMARY KEY(IDCONGRESSO,IDPESSOA);
ALTER TABLE EDITORACAO ADD CONSTRAINT FK_EDITOR_CONGRESSO FOREIGN KEY(IDCONGRESSO) REFERENCES CONGRESSO(IDCONGRESSO);
ALTER TABLE EDITORACAO ADD CONSTRAINT FK_EDITOR_PESSOA FOREIGN KEY(IDPESSOA) REFERENCES PESSOA(IDPESSOA);

CREATE TABLE AUTORES(
  IDPESSOA INTEGER,
  IDDOCUMENTO INTEGER
);
ALTER TABLE AUTORES ADD CONSTRAINT FK_AUTOR_PESSOA FOREIGN KEY(IDPESSOA) REFERENCES PESSOA(IDPESSOA); 
ALTER TABLE AUTORES ADD CONSTRAINT FK_AUTOR_DOCUMENTO FOREIGN KEY(IDDOCUMENTO) REFERENCES DOCUMENTO(IDDOCUMENTO);
ALTER TABLE AUTORES ADD CONSTRAINT PK_AUTORES PRIMARY KEY(IDPESSOA,IDDOCUMENTO);

INSERT INTO TIPORELACIONAMENTO VALUES(DEFAULT, 1, 'S','Autoria');


--Procedures

CREATE OR REPLACE FUNCTION carregaRelacionamentos() RETURNS void AS $$   
DECLARE   
     registrosDocumento record;
     varTipoRel integer;  
BEGIN   

--Apaga registros da tabela de relacionamentos
delete from relacionamento;

     --seleciona as pessoas que fazem parte dos documentos
     FOR registrosDocumento IN    
          select idDocumento, idPessoa
	    from autores
	group by idDocumento, idpessoa
	order by idDocumento
     LOOP   

     	 --Seleciona o tipo do relacionamento
	 select idTipoRel into varTipoRel
	   from tipoRelacionamento
	  where tipo = 'Autoria'
	    and Status = 'S';

	--Seleciona todos os autores que estao se relacionando com o autor atual nesse documento
	insert into relacionamento
	select registrosDocumento.idPessoa, idPessoa, varTipoRel, registrosDocumento.idDocumento
	  from autores
	 where idDocumento = registrosDocumento.idDocumento
	   and idPessoa <> registrosDocumento.idPessoa;
   
     END LOOP;   
     RETURN;   
END;   
$$ LANGUAGE plpgsql;


-- Triggers
-- CREATE OR REPLACE FUNCTION relPes2Null() 
-- RETURNS trigger LANGUAGE plpgsql
-- AS
--     'begin
--        new.idPessoa2 := null;  
--        return new;
--      end';
-- DROP TRIGGER REL_BI ON RELACIONAMENTO     	
-- CREATE TRIGGER REL_BI 
--   BEFORE INSERT ON RELACIONAMENTO
--   FOR EACH ROW
--   WHEN (NEW.idPEssoa2 = 0)
--   EXECUTE PROCEDURE relPes2Null(); 	
-- END;

CREATE UNIQUE INDEX ind_idpessoa_pessoa ON pessoa (idpessoa);

CREATE UNIQUE INDEX ind_idpessoa_nome ON nome (idpessoa);
CREATE INDEX ind_nome_nome_normal ON nome (nome);
CREATE INDEX ind_nome_nome ON nome (lower(nome));

CREATE UNIQUE INDEX ind_iddocumento_documento ON documento (iddocumento);
CREATE INDEX ind_titulo_documento ON documento (lower(titulo));

CREATE UNIQUE INDEX ind_idmeiop_meiopublicacao ON meiopublicacao (idmeiop);
CREATE INDEX ind_nome_meiopublicacao ON meiopublicacao (lower(nome));
